<?php
/**
 * Created by PhpStorm.
 * User: g
 * Date: 2018/3/14
 * Time: 13:08
 */

namespace app\index\controller;
use think\Controller;
use think\Session;
use think\Request;
use think\View;
use think\Db;
use think\Validate;
use app\index\validate\Valid;

class Bfqdbb extends Common
{

    /**
     * 我的拜访签到报表
     */
    public function mybfqdbb(){
        $date = $this->request->param('date');


        if(!$date){
            $date = date('Y-m-d');
        }
        $this->assign('date',$date);
        $count = db('yd_kq_em')->count();
        $data = db('yd_kq_em')->paginate(10,$count);
        $page = $data->render();
        $data = $data->items();
        foreach($data as $k=>$v){
            $v['xm_name'] = db('employee')->where('id',$v['xm'])->value('em_name');
            $v['kqcs'] = db('yd_kq_log')->where(['em_id'=>$v['xm'],'date'=>$date])->count();
            $num1 = db('yd_jxsbf')->where(['bfr'=>$v['xm'],'bgrq'=>$date])->count();
            $num2 = db('yd_schdbf')->where(['hbr'=>$v['xm'],'dwrq'=>$date])->count();
            $num3 = db('yd_wdbf')->where(['bfr'=>$v['xm'],'rq'=>$date])->count();
            $v['bfzs'] = $num1+$num2+$num3;
            $v['ybfjxs'] = $num1;
            $v['ybfwd'] = $num3;
            $v['schd'] = $num2;
            $data[$k] = $v;
        }
        $this->assign('data',$data);
        $this->assign('page',$page);
        return $this->fetch();
    }

    /**
     * 我的拜访签到报表--周报表
     */
    public function mybfqdbb_zbb(){
        $date = $this->request->param('date');
        $week = [];
        for($i=1;$i<=52;$i++){
            $week[date('Y年-第'.$i.'周')] = ['i'=>$i,'title'=>date('Y年-第'.$i.'周')];
        }
        $this->assign('week',$week);
        if(!$date){
            $date = $week[date('Y年-第W周')]['i'];
        }

        $this->assign('date',$date);

        $date = date('Y-m-d',$date*7*24*60*60+strtotime('Y-1-1 0:0:0'));
        $date2 = date('Y-m-d',$date*7*24*60*60+strtotime('Y-1-1 0:0:0')+24*60*60);
        $date = [['>',$date],['<',$date2],'and'];
        $count = db('yd_kq_em')->count();
        $data = db('yd_kq_em')->paginate(10,$count);
        $page = $data->render();
        $data = $data->items();
        foreach($data as $k=>$v){
            $v['xm_name'] = db('employee')->where('id',$v['xm'])->value('em_name');
            $v['kqcs'] = db('yd_kq_log')->where(['em_id'=>$v['xm'],'date'=>$date])->count();
            $num1 = db('yd_jxsbf')->where(['bfr'=>$v['xm'],'bgrq'=>$date])->count();
            $num2 = db('yd_schdbf')->where(['hbr'=>$v['xm'],'dwrq'=>$date])->count();
            $num3 = db('yd_wdbf')->where(['bfr'=>$v['xm'],'rq'=>$date])->count();
            $v['bfzs'] = $num1+$num2+$num3;
            $v['ybfjxs'] = $num1;
            $v['ybfwd'] = $num3;
            $v['schd'] = $num2;
            $data[$k] = $v;
        }
        $this->assign('data',$data);
        $this->assign('page',$page);
        return $this->fetch();
    }

    /**
     * 我的拜访签到报表--月报表
     */
    public function mybfqdbb_ybb(){
        $date = $this->request->param('date');

        $month = [];
        for($i=1;$i<=12;$i++){
            $month[date('Y年-'.$i.'月')] = ['i'=>$i,'title'=>date('Y年-'.$i.'月')];
        }
        $this->assign('month',$month);
        if(!$date){
            $date = $month[date('Y年-').trim(date('m'),'0').'月']['i'];
        }
        $this->assign('date',$date);
        $date = date('Y').'-'.$date.'-1';
        $date2 = date('Y').'-'.($date+1).'-1';
        $date = [['>=',$date],['<',$date2],'and'];
        $count = db('yd_kq_em')->count();
        $data = db('yd_kq_em')->paginate(10,$count);
        $page = $data->render();
        $data = $data->items();
        foreach($data as $k=>$v){
            $v['xm_name'] = db('employee')->where('id',$v['xm'])->value('em_name');
            $v['kqcs'] = db('yd_kq_log')->where(['em_id'=>$v['xm'],'date'=>$date])->count();
            $num1 = db('yd_jxsbf')->where(['bfr'=>$v['xm'],'bgrq'=>$date])->count();
            $num2 = db('yd_schdbf')->where(['hbr'=>$v['xm'],'dwrq'=>$date])->count();
            $num3 = db('yd_wdbf')->where(['bfr'=>$v['xm'],'rq'=>$date])->count();
            $v['bfzs'] = $num1+$num2+$num3;
            $v['ybfjxs'] = $num1;
            $v['ybfwd'] = $num3;
            $v['schd'] = $num2;
            $data[$k] = $v;
        }
        $this->assign('data',$data);
        $this->assign('page',$page);
        return $this->fetch();
    }

    /**
     * 经销商年度拜访报表
     */
    public function agencyannualbfbb(){
        $zoning = $this->request->param('zoning');
        $provice = $this->request->param('provice');
        $city = $this->request->param('city');
        $year = $this->request->param('year');
        $jxs = $this->request->param('jxs');
        $this->assign([
            'zoning'=>$zoning,
            'provice'=>$provice,
            'city'=>$city,
            'year'=>$year,
            'jxs'=>$jxs
        ]);
        $where = [];
        if($zoning){
            $where['t.zoning'] = $zoning;
        }
        if($provice){
            $where['t.provice'] = $provice;
        }
        if($city){
            $where['t.city'] = $city;
        }
        if($jxs){
            $where['t.title'] = $jxs;
        }

        $count = db('cg_jxs t')->join('cg_market_city t2','t.city=t2.id')->join('cg_market_provice t3','t3.id=t.provice')->join('cg_market_zoning t4','t4.id=t.zoning')->field('t.title,t2.title t2,t3.title t3,t4.title t4')->where($where)->count();
        $data = db('cg_jxs t')->join('cg_market_city t2','t.city=t2.id')->join('cg_market_provice t3','t3.id=t.provice')->join('cg_market_zoning t4','t4.id=t.zoning')->field('t.title,t2.title t2,t3.title t3,t4.title t4')->where($where)->paginate(10,$count);
        $page = $data->render();
        $data = $data->items();
        foreach($data as $k=>$v){
            $v['nbfcs'] = db('yd_jxsbf')->where(['bgrq'=>[['>=',$year.'-1-1'],['<=',$year.'-12-31'],'and']])->count();
            $v['m1'] = db('yd_jxsbf')->where(['bgrq'=>[['>=',$year.'-1-1'],['<',$year.'-2-1'],'and']])->count();
            $v['m2'] = db('yd_jxsbf')->where(['bgrq'=>[['>=',$year.'-2-1'],['<=',$year.'-2-28'],'and']])->count();
            $v['m3'] = db('yd_jxsbf')->where(['bgrq'=>[['>=',$year.'-3-1'],['<=',$year.'-3-31'],'and']])->count();
            $v['m4'] = db('yd_jxsbf')->where(['bgrq'=>[['>=',$year.'-4-1'],['<=',$year.'-4-30'],'and']])->count();
            $v['m5'] = db('yd_jxsbf')->where(['bgrq'=>[['>=',$year.'-5-1'],['<=',$year.'-5-31'],'and']])->count();
            $v['m6'] = db('yd_jxsbf')->where(['bgrq'=>[['>=',$year.'-6-1'],['<=',$year.'-6-30'],'and']])->count();
            $v['m7'] = db('yd_jxsbf')->where(['bgrq'=>[['>=',$year.'-7-1'],['<=',$year.'-7-31'],'and']])->count();
            $v['m8'] = db('yd_jxsbf')->where(['bgrq'=>[['>=',$year.'-8-1'],['<=',$year.'-8-31'],'and']])->count();
            $v['m9'] = db('yd_jxsbf')->where(['bgrq'=>[['>=',$year.'-9-1'],['<=',$year.'-9-30'],'and']])->count();
            $v['m10'] = db('yd_jxsbf')->where(['bgrq'=>[['>=',$year.'-10-1'],['<=',$year.'-10-31'],'and']])->count();
            $v['m11'] = db('yd_jxsbf')->where(['bgrq'=>[['>=',$year.'-11-1'],['<=',$year.'-11-30'],'and']])->count();
            $v['m12'] = db('yd_jxsbf')->where(['bgrq'=>[['>=',$year.'-12-1'],['<=',$year.'-12-31'],'and']])->count();
            $data[$k] = $v;
        }
        $this->assign([
            'page'=>$page,
            'data'=>$data,
        ]);
        //查询分区
        $fq = db('cg_market_zoning')->field('id,title')->select();
        $this->assign('fq',$fq);
        return $this->fetch();
    }

    /**
     * 下属拜访签到报表
     */
    public function xiashubfqdbb(){
        $date = $this->request->param('date');
        $xm = $this->request->param('xm');
        $bm = $this->request->param('bm');
        $this->assign([
            'xm'=>$xm,
            'bm'=>$bm,
        ]);
        if(!$date){
            $date = date('Y-m-d');
        }
        $where = [];
        $role_id = db('users_role t')->where(['users_id'=>session('login_id')])->value('role_id');
        $user_id = db('role t')->join('users_role t2','t2.role_id=t.id')->where('t.parent_id',$role_id)->column('users_id');
        $em_id = db('users')->where('id','in',$user_id)->column('employee_id');
        $where['xm'] = ['in',$em_id];
        if($xm){
            $where['xm_name'] = ['like',"%{$xm}%"];
        }
        if($bm){
            $where['zbm'] = ['like',"%{$bm}%"];
        }
        $this->assign('date',$date);
        $count = db('yd_kq_em')->where($where)->count();
        $data = db('yd_kq_em')->where($where)->paginate(10,$count);
        $page = $data->render();
        $data = $data->items();
        foreach($data as $k=>$v){
            $v['xm_name'] = db('employee')->where('id',$v['xm'])->value('em_name');
            $v['kqcs'] = db('yd_kq_log')->where(['em_id'=>$v['xm'],'date'=>$date])->count();
            $num1 = db('yd_jxsbf')->where(['bfr'=>$v['xm'],'bgrq'=>$date])->count();
            $num2 = db('yd_schdbf')->where(['hbr'=>$v['xm'],'dwrq'=>$date])->count();
            $num3 = db('yd_wdbf')->where(['bfr'=>$v['xm'],'rq'=>$date])->count();
            $v['bfzs'] = $num1+$num2+$num3;
            $v['ybfjxs'] = $num1;
            $v['ybfwd'] = $num3;
            $v['schd'] = $num2;
            $data[$k] = $v;
        }
        $this->assign('data',$data);
        $this->assign('page',$page);
        return $this->fetch();
    }

    /**
     * 下属拜访签到报表--周报表
     */
    public function xiashubfqdbb_zbb(){
        $date = $this->request->param('date');
        $xm = $this->request->param('xm');
        $bm = $this->request->param('bm');
        $this->assign([
            'xm'=>$xm,
            'bm'=>$bm,
        ]);

        $week = [];
        for($i=1;$i<=52;$i++){
            $week[date('Y年-第'.$i.'周')] = ['i'=>$i,'title'=>date('Y年-第'.$i.'周')];
        }
        $this->assign('week',$week);
        if(!$date){
            $date = $week[date('Y年-第W周')]['i'];
        }

        $this->assign('date',$date);

        $date = date('Y-m-d',$date*7*24*60*60+strtotime('Y-1-1 0:0:0'));
        $date2 = date('Y-m-d',$date*7*24*60*60+strtotime('Y-1-1 0:0:0')+24*60*60);
        $date = [['>',$date],['<',$date2],'and'];
        $where = [];
        if($xm){
            $where['xm'] = ['like',"%{$xm}%"];
        }
        if($bm){
            $where['zbm'] = ['like',"%{$bm}%"];
        }
        $count = db('yd_kq_em')->where($where)->count();
        $data = db('yd_kq_em')->where($where)->paginate(10,$count);
        $page = $data->render();
        $data = $data->items();
        foreach($data as $k=>$v){
            $v['xm_name'] = db('employee')->where('id',$v['xm'])->value('em_name');
            $v['kqcs'] = db('yd_kq_log')->where(['em_id'=>$v['xm'],'date'=>$date])->count();
            $num1 = db('yd_jxsbf')->where(['bfr'=>$v['xm'],'bgrq'=>$date])->count();
            $num2 = db('yd_schdbf')->where(['hbr'=>$v['xm'],'dwrq'=>$date])->count();
            $num3 = db('yd_wdbf')->where(['bfr'=>$v['xm'],'rq'=>$date])->count();
            $v['bfzs'] = $num1+$num2+$num3;
            $v['ybfjxs'] = $num1;
            $v['ybfwd'] = $num3;
            $v['schd'] = $num2;
            $data[$k] = $v;
        }
        $this->assign('data',$data);
        $this->assign('page',$page);
        return $this->fetch();
    }

    /**
     * 下属拜访签到报表--月报表
     */
    public function xiashubfqdbb_ybb(){
        $date = $this->request->param('date');
        $xm = $this->request->param('xm');
        $bm = $this->request->param('bm');
        $this->assign([
            'xm'=>$xm,
            'bm'=>$bm,
        ]);

        $month = [];
        for($i=1;$i<=12;$i++){
            $month[date('Y年-'.$i.'月')] = ['i'=>$i,'title'=>date('Y年-'.$i.'月')];
        }
        $this->assign('month',$month);
        if(!$date){
            $date = $month[date('Y年-').trim(date('m'),'0').'月']['i'];
        }
        $this->assign('date',$date);
        $date = date('Y').'-'.$date.'-1';
        $date2 = date('Y').'-'.($date+1).'-1';
        $date = [['>=',$date],['<',$date2],'and'];

        $where = [];
        if($xm){
            $where['xm'] = ['like',"%{$xm}%"];
        }
        if($bm){
            $where['zbm'] = ['like',"%{$bm}%"];
        }

        $count = db('yd_kq_em')->where($where)->count();
        $data = db('yd_kq_em')->where($where)->paginate(10,$count);
        $page = $data->render();
        $data = $data->items();
        foreach($data as $k=>$v){
            $v['xm_name'] = db('employee')->where('id',$v['xm'])->value('em_name');
            $v['kqcs'] = db('yd_kq_log')->where(['em_id'=>$v['xm'],'date'=>$date])->count();
            $num1 = db('yd_jxsbf')->where(['bfr'=>$v['xm'],'bgrq'=>$date])->count();
            $num2 = db('yd_schdbf')->where(['hbr'=>$v['xm'],'dwrq'=>$date])->count();
            $num3 = db('yd_wdbf')->where(['bfr'=>$v['xm'],'rq'=>$date])->count();
            $v['bfzs'] = $num1+$num2+$num3;
            $v['ybfjxs'] = $num1;
            $v['ybfwd'] = $num3;
            $v['schd'] = $num2;
            $data[$k] = $v;
        }
        $this->assign('data',$data);
        $this->assign('page',$page);
        return $this->fetch();
    }

    /**
     * 所有拜访签到报表
     */
    public function allbfqdbb(){
        $date = $this->request->param('date');

        if(!$date){
            $date = date('Y-m-d');
        }
        $this->assign('date',$date);
        $count = db('yd_kq_em')->count();
        $data = db('yd_kq_em')->paginate(10,$count);
        $page = $data->render();
        $data = $data->items();
        foreach($data as $k=>$v){
            $v['xm_name'] = db('employee')->where('id',$v['xm'])->value('em_name');
            $v['kqcs'] = db('yd_kq_log')->where(['em_id'=>$v['xm'],'date'=>$date])->count();
            $num1 = db('yd_jxsbf')->where(['bfr'=>$v['xm'],'bgrq'=>$date])->count();
            $num2 = db('yd_schdbf')->where(['hbr'=>$v['xm'],'dwrq'=>$date])->count();
            $num3 = db('yd_wdbf')->where(['bfr'=>$v['xm'],'rq'=>$date])->count();
            $v['bfzs'] = $num1+$num2+$num3;
            $v['ybfjxs'] = $num1;
            $v['ybfwd'] = $num3;
            $v['schd'] = $num2;
            $data[$k] = $v;
        }
        $this->assign('data',$data);
        $this->assign('page',$page);
        return $this->fetch();
    }

    /**
     * 所有拜访签到报表
     */
    public function allbfqdbb_zbb(){
        $date = $this->request->param('date');

        $xm = $this->request->param('xm');
        $bm = $this->request->param('bm');
        $this->assign([
            'xm'=>$xm,
            'bm'=>$bm,
        ]);

        $week = [];
        for($i=1;$i<=52;$i++){
            $week[date('Y年-第'.$i.'周')] = ['i'=>$i,'title'=>date('Y年-第'.$i.'周')];
        }
        $this->assign('week',$week);
        if(!$date){
            $date = $week[date('Y年-第W周')]['i'];
        }

        $this->assign('date',$date);

        $date = date('Y-m-d',$date*7*24*60*60+strtotime('Y-1-1 0:0:0'));
        $date2 = date('Y-m-d',$date*7*24*60*60+strtotime('Y-1-1 0:0:0')+24*60*60);
        $date = [['>',$date],['<',$date2],'and'];
        $where = [];
        if($xm){
            $where['xm'] = ['like',"%{$xm}%"];
        }
        if($bm){
            $where['zbm'] = ['like',"%{$bm}%"];
        }
        $count = db('yd_kq_em')->count();
        $data = db('yd_kq_em')->paginate(10,$count);
        $page = $data->render();
        $data = $data->items();
        foreach($data as $k=>$v){
            $v['xm_name'] = db('employee')->where('id',$v['xm'])->value('em_name');
            $v['kqcs'] = db('yd_kq_log')->where(['em_id'=>$v['xm'],'date'=>$date])->count();
            $num1 = db('yd_jxsbf')->where(['bfr'=>$v['xm'],'bgrq'=>$date])->count();
            $num2 = db('yd_schdbf')->where(['hbr'=>$v['xm'],'dwrq'=>$date])->count();
            $num3 = db('yd_wdbf')->where(['bfr'=>$v['xm'],'rq'=>$date])->count();
            $v['bfzs'] = $num1+$num2+$num3;
            $v['ybfjxs'] = $num1;
            $v['ybfwd'] = $num3;
            $v['schd'] = $num2;
            $data[$k] = $v;
        }
        $this->assign('data',$data);
        $this->assign('page',$page);
        return $this->fetch();
    }
    /**
     * 所有拜访签到报表
     */
    public function allbfqdbb_ybb(){
        $date = $this->request->param('date');
        $xm = $this->request->param('xm');
        $bm = $this->request->param('bm');
        $this->assign([
            'xm'=>$xm,
            'bm'=>$bm,
        ]);

        $month = [];
        for($i=1;$i<=12;$i++){
            $month[date('Y年-'.$i.'月')] = ['i'=>$i,'title'=>date('Y年-'.$i.'月')];
        }
        $this->assign('month',$month);
        if(!$date){
            $date = $month[date('Y年-').trim(date('m'),'0').'月']['i'];
        }
        $this->assign('date',$date);
        $date = date('Y').'-'.$date.'-1';
        $date2 = date('Y').'-'.($date+1).'-1';
        $date = [['>=',$date],['<',$date2],'and'];

        $where = [];
        if($xm){
            $where['xm'] = ['like',"%{$xm}%"];
        }
        if($bm){
            $where['zbm'] = ['like',"%{$bm}%"];
        }

        $count = db('yd_kq_em')->where($where)->count();
        $data = db('yd_kq_em')->where($where)->paginate(10,$count);
        $page = $data->render();
        $data = $data->items();
        foreach($data as $k=>$v){
            $v['xm_name'] = db('employee')->where('id',$v['xm'])->value('em_name');
            $v['kqcs'] = db('yd_kq_log')->where(['em_id'=>$v['xm'],'date'=>$date])->count();
            $num1 = db('yd_jxsbf')->where(['bfr'=>$v['xm'],'bgrq'=>$date])->count();
            $num2 = db('yd_schdbf')->where(['hbr'=>$v['xm'],'dwrq'=>$date])->count();
            $num3 = db('yd_wdbf')->where(['bfr'=>$v['xm'],'rq'=>$date])->count();
            $v['bfzs'] = $num1+$num2+$num3;
            $v['ybfjxs'] = $num1;
            $v['ybfwd'] = $num3;
            $v['schd'] = $num2;
            $data[$k] = $v;
        }
        $this->assign('data',$data);
        $this->assign('page',$page);
        return $this->fetch();
    }

}